Wait Statistics
wang fulong: collect database backgroud wait event statistics http://www.praetoriate.com/oracle_tips_detail_analysis.htm select b.sid c1, decode(b.username,NULL,c.name,b.username) c2, event c3, a.total_waits c4, round((a.time_waited / 100),2) c5, a.total_timeouts c6, round((average_wait / 100),2) c7, round((a.max_wait / 100),2) c8 from sys.v_$session_event a, sys.v_$session b, sys.v_$bgprocess c where event NOT LIKE 'DFS%' and event NOT LIKE 'KXFX%' and a.sid = b.sid and b.paddr = c.paddr (+) and event NOT IN ( 'lock element cleanup', 'pmon timer', 'rdbms ipc message', 'smon timer', 'SQL*Net message from client', 'SQL*Net break/reset to client', 'SQL*Net message to client', 'SQL*Net more data to client', 'dispatcher timer', 'Null event', 'io done', 'parallel query dequeue wait', 'parallel query idle wait - Slaves', 'pipe get', 'PL/SQL lock timer', 'slave wait', 'virtual circuit status', 'WMON goes to sleep' ) order by 4 desc ; http://www.praetoriate.com/oracle_tips_current_events.htm set pages 999 set lines 90 column c1 heading 'Event|Name' format a30 column c2 heading 'Total|Waits' format 999,999,999 column c3 heading 'Seconds|Waiting' format 999,999 column c4 heading 'Total|Timeouts' format 999,999,999 column c5 heading 'Average|Wait|(in secs)' format 99.999 ttitle 'System-wide Wait Analysis|for current wait events' select event c1, total_waits c2, time_waited / 100 c3, total_timeouts c4, average_wait /100 c5 from sys.v_$system_event where event not in ( 'dispatcher timer', 'lock element cleanup', 'Null event', 'parallel query dequeue wait', 'parallel query idle wait - Slaves', 'pipe get', 'PL/SQL lock timer', 'pmon timer', 'rdbms ipc message', 'slave wait', 'smon timer', 'SQL*Net break/reset to client', 'SQL*Net message from client', 'SQL*Net message to client', 'SQL*Net more data to client', 'virtual circuit status', 'WMON goes to sleep' ) AND event not like 'DFS%' and event not like '%done%' and event not like '%Idle%' AND event not like 'KXFX%' order by c2 desc ; 2.6. sowas liesse sich mit state-file nachbauen http://www.pafumi.net/Wait_Events.html#stastpack_wait_events -- prompt -- prompt -- prompt *********************************************************** -- prompt Excessive waits on background events -- prompt *********************************************************** -- promptttitle 'High waits on background events|Rollup by hour' column mydate heading 'Yr. Mo Dy Hr' format a13; column event format a30; column total_waits heading 'tot waits' format 999,999; column time_waited heading 'time wait' format 999,999; column total_timeouts heading 'timeouts' format 9,999; break on to_char(snap_time,'yyyy-mm-dd') skip 1; select to_char(snap_time,'yyyy-mm-dd HH24') mydate, e.event, e.total_waits - nvl(b.total_waits,0) total_waits, e.time_waited - nvl(b.time_waited,0) time_waited, e.total_timeouts - nvl(b.total_timeouts,0) total_timeouts from stats$bg_event_summary b, stats$bg_event_summary e, stats$snapshot sn where snap_time > sysdate-&1 and e.event not like '%timer' and e.event not like '%message%' and e.event not like '%slave wait%' and e.snap_id = sn.snap_id and b.snap_id = e.snap_id-1 and b.event = e.event and e.total_timeouts > 100 and (e.total_waits - b.total_waits > 100 or e.time_waited - b.time_waited > 100 ); wobei die events insert into stats$bg_event_summary ( snap_id , dbid , instance_number , event , total_waits , total_timeouts , time_waited_micro ) select l_snap_id , p_dbid , p_instance_number , e.event , sum(e.total_waits) , sum(e.total_timeouts) , sum(e.time_waited_micro) from v$session_event e where e.sid in (select s.sid from v$session s where s.type = 'BACKGROUND') group by l_snap_id, p_dbid, p_instance_number, e.event; SQL> select e.event,sum(e.total_waits),sum(e.total_timeouts), sum(e.time_waited_micro),sum(average_wait) from v$session_event e where e.sid in (select s.sid from v$session s where s.type = 'BACKGROUND') group by e.event; EVENT SUM(E.TOTAL_WAITS) SUM(E.TOTAL_TIMEOUTS) SUM(E.TIME_WAITED_MICRO) SUM(AVERAGE_WAIT) ---------------------------------------------------------------- ------------------ --------------------- ------------------------ ----------------- LGWR wait for redo copy 763401 344 11516998 0 buffer busy waits 421 0 1717649 0 control file parallel write 5655251 0 1.5126E+10 1 control file sequential read 128551 0 12213216 0 db file parallel write 903737 0 1707478 0 db file scattered read 1529 0 80323875 5 db file sequential read 798 0 19732468 3 direct path read 9327 0 92967 0 direct path write 9315 0 5861 0 enqueue 24 0 3979622 19 latch free 2185 356 136999262 659 EVENT SUM(E.TOTAL_WAITS) SUM(E.TOTAL_TIMEOUTS) SUM(E.TIME_WAITED_MICRO) SUM(AVERAGE_WAIT) ---------------------------------------------------------------- ------------------ --------------------- ------------------------ ----------------- library cache load lock 2 0 236 0 log buffer space 1 0 21646 2 log file parallel write 8542735 0 2.5017E+10 0 log file sequential read 1752 0 1007043 0 log file single write 1752 0 10815699 1 pmon timer 5666251 5665953 1.6552E+13 292 rdbms ipc message 24996488 16933625 6.2766E+13 140395 rdbms ipc reply 7145 2 70856834 1 row cache lock 1 0 141 0 smon timer 98659 56200 1.6000E+13 16217 pro event ein statefile, dann differenz ausrechnen und bei überschreiten alarmieren. Allerdings gibt das ewig lange Performancedaten. Ein Service pro Event ist auch blöd.